﻿using DB;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TrainTicketBookingSystem
{
    internal class CarriageService
    {
       public List<Carriage> FindAllCarriage()
        {
            List<Carriage> carriagesList = new List<Carriage>();

            string sql = " select * from carriage ";
            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql);

            while (reader.Read())
            {
                int CarriageId = reader.GetInt32("carriageId");
                int TrainId = reader.GetInt32("trainId");
                int CarriageNum = reader.GetInt32("carriageNum");
                int Price = reader.GetInt32("Price");

                Carriage carriage = new Carriage(CarriageId,TrainId,CarriageNum,Price);
                carriagesList.Add(carriage);
            }

            return carriagesList;
        }

        public int FindHighSeatNumByTrainId(int TrainId)
        {
            string sql = " select count(*) from carriage where trainId = @TrainId and Price = '800' or Price = '200' ";
            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql,new MySqlParameter("@TrainId", TrainId));
            if(reader.Read())
            {
                return reader.GetInt32("count(*)") * 10;
            }
            return 0;
        }

        public int FindLowSeatNumByTrainId(int TrainId)
        {
            string sql = " select count(*) from carriage where trainId = @TrainId and Price = '400' or Price = '150' ";
            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql, new MySqlParameter("@TrainId", TrainId));
            if (reader.Read())
            {
                return reader.GetInt32("count(*)") * 10;
            }
            return 0;
        }

        public int FindPriceBySeatId(int SeatId)
        {
            string sql = " select * from carriage join seat on seat.carriageId = carriage.carriageId " +
                " where seatId = @SeatId ";

            MySqlDataReader reader = MySqlUtil.ExecutQuery(sql, new MySqlParameter("@seatId",SeatId));

            if (reader.Read())
            {
                return reader.GetInt32("price");
            }

            return 0;
        }
    }
}
